package dao.common;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import db.common.DBHelper;
import model.common.Goods;
import model.common.Repertory;

public class RepertoryDao {
	
	public void addRepertory(Repertory r) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " "+"insert into tab_repertory " + "(goodsName,goodsRepertoryNumber,goodsUnitPrice,goodsPrice,"
				    + " goodsManufacturer,goodsProductionDate,goodsExpirationDate,goodsStockDate) "
				    + " values(?,?,?,?,?,?,?,?) ";
		
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setString(1, r.getGoodsName());
		ptmt.setInt(2, r.getGoodsRepertoryNumber());
		ptmt.setDouble(3, r.getGoodsUnitPrice());
		ptmt.setDouble(4, r.getGoodsPrice());
		ptmt.setString(5, r.getGoodsManufacturer());
		ptmt.setDate(6, new java.sql.Date(r.getGoodsProductionDate().getTime()));
		ptmt.setDate(7, new java.sql.Date(r.getGoodsExpirationDate().getTime()));
		ptmt.setDate(8, new java.sql.Date(r.getGoodsStockDate().getTime()));
		
		ptmt.execute();
	}
	
	public void deleteRepertory(Integer goodsId) throws SQLException {
		Connection conn = DBHelper.getConnection();
		String sql = " delete from tab_repertory  where goodsId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsId);
		ptmt.execute();
	}

	public void updateRepertory(Repertory r) throws SQLException{
		Connection conn=DBHelper.getConnection();
		String sql = " update tab_repertory set goodsName=?,goodsRepertoryNumber=?,goodsUnitPrice=?,goodsPrice=?, "
				+ " goodsManufacturer=?,goodsProductionDate=?,goodsExpirationDate=?,goodsStockDate=? "
				+ " where goodsId=? ";
		PreparedStatement ptmt=conn.prepareStatement(sql);
		
		ptmt.setString(1, r.getGoodsName());
		ptmt.setDouble(2, r.getGoodsRepertoryNumber());
		ptmt.setDouble(3, r.getGoodsUnitPrice());
		ptmt.setDouble(4, r.getGoodsPrice());
		ptmt.setString(5, r.getGoodsManufacturer());
		ptmt.setDate(6, new java.sql.Date(r.getGoodsProductionDate().getTime()));
		ptmt.setDate(7, new java.sql.Date(r.getGoodsExpirationDate().getTime()));
		ptmt.setDate(8, new java.sql.Date(r.getGoodsStockDate().getTime()));
		ptmt.setInt(9, r.getGoodsId());
		
		ptmt.execute();
		/*ptmt.close();
		conn.close();*/
	}
	
	public Repertory get(Integer goodsId) throws SQLException {
		Repertory r = null;
		Connection conn = DBHelper.getConnection();
		String sql = " select * from tab_repertory where goodsId=? ";
		PreparedStatement ptmt = conn.prepareStatement(sql);
		ptmt.setInt(1, goodsId);
		ResultSet rs = ptmt.executeQuery();
		while (rs.next()) {
			r = new Repertory();
			r.setGoodsId(rs.getInt("goodsId"));
			r.setGoodsName(rs.getString("goodsName"));
			r.setGoodsRepertoryNumber(rs.getInt("goodsRepertoryNumber"));
			r.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
			r.setGoodsPrice(rs.getDouble("goodsPrice"));
			r.setGoodsManufacturer(rs.getString("goodsManufacturer"));
			r.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
			r.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));
			r.setGoodsStockDate(rs.getDate("goodsStockDate"));
			
		}
		rs.close();
		ptmt.close();
		conn.close();
		return r;
	}
	
	public List<Repertory> query(List<Map<String, Object>> params) throws SQLException{
		List<Repertory> result=new ArrayList<Repertory>(); 
		Connection conn = DBHelper.getConnection();
		StringBuffer sb=new StringBuffer();
		sb.append("select * from tab_repertory where 1=1 ");
		if(params!=null&&params.size()>0){
			for(int i=0;i<params.size();i++){
				Map<String, Object> map=params.get(i);
				sb.append(" and " +map.get("name")+"  " +map.get("rela")+"  " +map.get("value"));
			}
		}
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());

		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		Repertory r = null;
		while (rs.next()) {
		r=new Repertory();
		r.setGoodsId(rs.getInt("goodsId"));
		r.setGoodsName(rs.getString("goodsName"));
		r.setGoodsRepertoryNumber(rs.getInt("goodsRepertoryNumber"));
		r.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
		r.setGoodsPrice(rs.getDouble("goodsPrice"));
		r.setGoodsManufacturer(rs.getString("goodsManufacturer"));
		r.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
		r.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));
		r.setGoodsStockDate(rs.getDate("goodsStockDate"));
		
			result.add(r);
		}
		
		return result;
		}
		
	public List<Repertory> query() throws Exception{
		List<Repertory> result=new ArrayList<Repertory>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select goodsId,goodsName,goodsPrice from tab_repertory ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		
		ResultSet rs=ptmt.executeQuery();
		
		Repertory r=null;
		while(rs.next()){
			r=new Repertory();
			r.setGoodsId(rs.getInt("goodsId"));
			r.setGoodsName(rs.getString("goodsName"));
			r.setGoodsPrice(rs.getDouble("goodsPrice"));
			result.add(r);
		}
		return result;
	}
	
	public List<Repertory> query(String goodsName,String goodsPrice) throws Exception{
		List<Repertory> result=new ArrayList<Repertory>();
		
		Connection conn=DBHelper.getConnection();
		StringBuilder sb=new StringBuilder();
		sb.append("select * from tab_repertory  ");
		
		sb.append(" where goodsName like ? and goodsPrice like ? ");
		
		PreparedStatement ptmt=conn.prepareStatement(sb.toString());
		ptmt.setString(1, "%"+goodsName+"%");
		ptmt.setString(2, "%"+goodsPrice+"%");
		
		System.out.println(sb.toString());
		ResultSet rs=ptmt.executeQuery();
		
		Repertory r=null;
		while(rs.next()){
			r=new Repertory();
			r.setGoodsId(rs.getInt("goodsId"));
			r.setGoodsName(rs.getString("goodsName"));
			r.setGoodsRepertoryNumber(rs.getInt("goodsRepertoryNumber"));
			r.setGoodsUnitPrice(rs.getDouble("goodsUnitPrice"));
			r.setGoodsPrice(rs.getDouble("goodsPrice"));
			r.setGoodsManufacturer(rs.getString("goodsManufacturer"));
			r.setGoodsProductionDate(rs.getDate("goodsProductionDate"));
			r.setGoodsExpirationDate(rs.getDate("goodsExpirationDate"));
			r.setGoodsStockDate(rs.getDate("goodsStockDate"));
			
			result.add(r);
		}
		return result;
	}


}

	

